package ronglian;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

import com.dudu.access.RongLianUtils;
import com.dudu.dataImport.DBConnectionUtil;

public class DelMediaFile {
	
	/**
	 * 删除容联服务器上的语音文件
	 * @param conn
	 */
	public void dataConversion(Connection conn, String beginTime, String endTime){
		Statement stmt = null;
		try{
			stmt=conn.createStatement();
			/*sql 拼接*/
			StringBuffer sql=new StringBuffer();
			sql.append(" SELECT DISTINCT(t.voice_name) FROM t_send_notice t " +
					"WHERE t.create_time > '"+beginTime+"' AND t.create_time < '"+endTime+"' " +
					"AND t.voice_type LIKE '%RongLian%' " +
					" order by t.create_time ");
			/*执行查询*/
			ResultSet accountresult = stmt.executeQuery(sql.toString());
			/*打印总条数*/
			accountresult.last();//移到最后一行   
			int rowCount = accountresult.getRow(); //得到当前行号，也就是记录数   
			accountresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
			//System.out.println("accountresultsql="+sql.toString()+",resultCount="+rowCount);
			if(rowCount>0){
				/*遍历accountresult*/
				while (accountresult.next()) {
					/*获取数据*/
					String voice_name = accountresult.getString("voice_name");
					if(StringUtils.isNotBlank(voice_name)){
						JSONObject resultJson = RongLianUtils.DelMediaFile(voice_name);
						if("0".equals(resultJson.getString("result"))){
							
						}else{
							System.out.println(voice_name);
						}
					}
				}
			}				
		}catch(Exception e){
			try {
				conn.rollback();
				conn.close();
		        stmt.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		/*
		 * 生产环境
		 * 上次清空日期2017-12-01
		 */
		String url="jdbc:mysql://182.92.147.67:3306/voip_plat_version4?useUnicode=true&amp;characterEncoding=UTF-8";
		String username="dudu_manager";
		String password="Ukefj93r*93kfle";
		
		/*
		 * 沙箱环境
		 * 上次清空日期2017-12-01
		 */
		/*String url="jdbc:mysql://172.20.8.236:3306/voip_dudu?useUnicode=true&amp;characterEncoding=UTF-8";
		String username="root";
		String password="duDu4open";*/
		
		/*
		 * 测试环境
		 * 上次清空日期2017-12-01
		 */
		/*String url="jdbc:mysql://172.20.8.244:9596/voip_dudu?useUnicode=true&amp;characterEncoding=UTF-8";
		String username="voip_plat";
		String password="YoyoVoip@2016";*/
		
		/*创建连接*/
		DBConnectionUtil dbUtil = new DBConnectionUtil();
		Connection conn = dbUtil.getDBConnection(url, username, password);
		/*执行*/
		DelMediaFile util=new DelMediaFile();
		String beginTime = "2016-10-27 00:00:00";
		String endTime = "2016-12-01 00:00:00";
		util.dataConversion(conn, beginTime, endTime);
		System.out.println("执行完成执行以下sql");
	}

}
